package com.neuedu.dbutil;

import com.neuedu.dbutil.entity.Role;
import com.neuedu.dbutil.entity.User;
import org.junit.Test;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.List;
import java.util.Map;

/**
 * @auth 金山老师
 * title:   书籍代码
 * creatime: 2022-10-12 14:45
 * 描述 :
 */

public class JDBCUtilsTest {


    private int abc;

    @Test
    public void update() {

        Connection conn = null;
        try {
            conn = JDBCUtils.getConnection();
            String sql = "UPDATE USER SET PASSWORD = ? WHERE id = ?";
            Object[] params = {"999999", 5};
            int count = JDBCUtils.update(conn, sql, params);
            System.out.println("count = " + count);

        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
            }
        }


    }


    @Test
    public void insert() {

        Connection conn = null;
        try {
            conn = JDBCUtils.getConnection();
            String sql = "INSERT INTO USER (username,PASSWORD) VALUES(?,?)";
            Object[] params = {"JDBCUTIL", "789456123"};
            int count = JDBCUtils.update(conn, sql, params);
            System.out.println("count = " + count);

        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
            }
        }


    }

    @Test
    public void selectList() throws SQLException {
        Connection conn = null;
        try {
            conn = JDBCUtils.getConnection();

            String sql  = "select * from role";
            List<Role> roles = JDBCUtils.selectList(conn,Role.class, sql);
            System.out.println("roles = " + roles);

        }catch (Exception e){
            e.printStackTrace();
        }finally {
            if(conn != null){
                conn.close();
            }
        }
    }

    @Test
    public void selectListUser() throws SQLException {
        Connection conn = null;
        try {
            conn = JDBCUtils.getConnection();

            String sql  = "select * from user";
            List<User> userList = JDBCUtils.selectList(conn,User.class, sql);

            for (User user : userList) {
                System.out.println(user);
            }

        }catch (Exception e){
            e.printStackTrace();
        }finally {
            if(conn != null){
                conn.close();
            }
        }
    }


    @Test
    public void testSelectListPage() throws SQLException {
        Connection conn = null;
        try {
            conn = JDBCUtils.getConnection();

            String sql  = "select * from user";
            int current = 1;
            int size = 10;

            //查询总条数
            long total = JDBCUtils.selectTotal(conn ,sql);



            List<User> userList = JDBCUtils.selectPage(conn,User.class, sql ,current,size);
            System.out.printf("总条数:%d 当前页号：%d\t pageSize:%d\r\n",total, current,size);
            for (User user : userList) {
                System.out.println(user);
            }
            System.out.println();
            System.out.println();

            current = 2;
            userList = JDBCUtils.selectPage(conn,User.class, sql ,current,size);
            System.out.printf("总条数:%d 当前页号：%d\t pageSize:%d\r\n",total, current,size);
            for (User user : userList) {
                System.out.println(user);
            }
            System.out.println();
            System.out.println();
            current = 3;
            userList = JDBCUtils.selectPage(conn,User.class, sql ,current,size);
            System.out.printf("总条数:%d 当前页号：%d\t pageSize:%d\r\n",total, current,size);
            for (User user : userList) {
                System.out.println(user);
            }

        }catch (Exception e){
            e.printStackTrace();
        }finally {
            if(conn != null){
                conn.close();
            }
        }
    }

    @Test
    public void selectObject() {
        Connection conn = null;
        try {
            conn = JDBCUtils.getConnection();

            String sql  = " SELECT username FROM USER t WHERE t.`id` = ?  ";
            int id = 1;
            String username  = (String) JDBCUtils.selectObject(conn, sql , id );

            System.out.println("username = " + username);


            String sql2 = "SELECT COUNT(1) COUNT FROM (select * from user) t ";
            long count = (long) JDBCUtils.selectObject(conn, sql2 );
            System.out.println("count = " + count);


        }catch (Exception e){
            e.printStackTrace();
        }finally {
            if(conn != null){
                try {
                    conn.close();
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
            }
        }
    }


    @Test
    public void selectOne() {

        Connection conn = null;
        try {
            conn = JDBCUtils.getConnection();

            String sql = "SELECT * FROM USER  WHERE username = ? AND PASSWORD = ? ";
            Object[] params = {"admin", "123456"};
            User user = JDBCUtils.selectOne(conn, User.class, sql, params);
            System.out.println(user);



            sql = " select * from role where role_id = ?";
            int id = 1;
            Role role = JDBCUtils.selectOne(conn,Role.class,sql,id);
            System.out.println("role = " + role);


            id = 1000;
            role = JDBCUtils.selectOne(conn,Role.class,sql,id);
            System.out.println("role = " + role); //期望 打印null


        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
            }
        }
    }

    @Test
    public void selectMapList() {

        Connection conn = null;
        try {
            conn = JDBCUtils.getConnection();

            String sql  = " SELECT gender name ,COUNT(1) value FROM USER GROUP BY gender  ";

            List<Map> list = JDBCUtils.selectMapList(conn, sql );

            list.forEach(System.out::println);


        }catch (Exception e){
            e.printStackTrace();
        }finally {
            if(conn != null){
                try {
                    conn.close();
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
            }
        }
    }





    @Test
    public void selectMap() {

        Connection conn = null;
        try {
            conn = JDBCUtils.getConnection();

            String sql  = " SELECT gender name ,COUNT(1) value FROM USER  where gender = 1 GROUP BY gender  ";

            Map map = JDBCUtils.selectMap(conn, sql );

            System.out.println("map = " + map);

        }catch (Exception e){
            e.printStackTrace();
        }finally {
            if(conn != null){
                try {
                    conn.close();
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
            }
        }
    }

}